﻿Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class frmXuatDuLieu
    Sub blank(ByVal str As String)

        If cmbChon.Text = "" Or txtDuongDan.Text = "" Then
            MsgBox("Vui lòng chọn nguồn xuất và đường dẫn!")
            Exit Sub
        Else
            If str = "xuat" Then

                Dim cnn As SqlConnection
                Dim connectionString As String
                Dim sql As String
                Dim i, j As Integer

                Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value

                xlApp = New Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")

                connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=QUANLYTHUENHA;Integrated Security=True"
                cnn = New SqlConnection(connectionString)
                cnn.Open()
                sql = String.Format("select * from [{0}]", cmbChon.Text)
                'sql = "SELECT * FROM KHACHHANGTHUENHA"
                Dim dscmd As New SqlDataAdapter(sql, cnn)
                Dim ds As New DataSet
                dscmd.Fill(ds)

                For i = 0 To ds.Tables(0).Rows.Count - 1
                    For j = 0 To ds.Tables(0).Columns.Count - 1
                        xlWorkSheet.Cells(i + 1, j + 1) = _
                        ds.Tables(0).Rows(i).Item(j)
                    Next
                Next

                Dim s As String
                s = SaveFileDialog1.FileName

                xlWorkSheet.SaveAs(s)
                xlWorkBook.Close()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)

                cnn.Close()

                MsgBox("Bạn có thể tìm thấy tập tin ở " & s)
            End If
        End If
    End Sub


    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub btnDuongDan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDuongDan.Click
        SaveFileDialog1.FileName = cmbChon.Text
        SaveFileDialog1.Title = "Chọn đường dẫn xuất dữ liệu!"
        SaveFileDialog1.Filter = "Excel (*.xls;*.xlsx)|*.xls;*.xlsx"
        SaveFileDialog1.ShowDialog()
        txtDuongDan.Text = SaveFileDialog1.FileName
    End Sub

    Private Sub btnXuat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXuat.Click
        blank("xuat")
    End Sub

    Private Sub btnHuy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHuy.Click
        Me.Close()
    End Sub
End Class